/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package df.bean.interfacefile;

import df.bean.db.conn.DBConnection;
import java.sql.ResultSet;
import java.sql.Statement;
import df.bean.db.conn.DBConn;
import df.bean.obj.util.*;

/**
 *
 * @author nopphadon
 */
public class ExportDFToBankBean extends InterfaceTextFileBean {
    private ResultSet rs;
    private Statement stm;
    private String payment_date = "";
    private String transaction_date = "";
    private String sum_dr_amount = "";
    @Override
    public boolean insertData(String fn, DBConnection d) {
        throw new UnsupportedOperationException("Not supported yet.");
    }
    
    public void setPaymentDate(String s){
    	this.payment_date = s;
    }
    public void setTransactionDate(String s){
    	this.transaction_date = s;
    }

    @Override
    public boolean exportData(String fn, String hp, String type, String year, String month, DBConn d, String path) {
        String[] sub_data = null;
        String qy = this.getOwnerBank();
        String dat = "SELECT FILE_TYPE, RECORD_TYPE, BATCH_NUMBER, RECEIVING_BANK_CODE," + //0-3
        "RECEIVING_BRANCH_CODE, RECEIVING_ACCOUNT_NO, SENDING_BANK_CODE, SENDING_BRANCH_CODE," + //4-7
        "SENDING_ACCOUNT_NO, EFFECTIVE_DATE, SERVICE_TYPE, CLEARING_HOUSE_CODE,"+ //8-11
        "CONVERT(INT,BANK_TMB_MEDIA_CLEARING.TRANSFER_AMOUNT) / 100.00 AS AMOUNT, RECEIVER_INFORMATION, SENDER_INFORMATION, OTHER_INFORMATION,"+ //12-15
        "REFERENCE_RUNNING_NO, SPACE, COMPANY_CODE, SEQUENCE_NUMBER "+ //16-19
        "FROM BANK_TMB_MEDIA_CLEARING WHERE (BATCH_NO IS NULL OR BATCH_NO = '') AND "+
        "YYYY LIKE '"+year+"' AND MM LIKE '"+month+"' AND SERVICE_TYPE LIKE '"+type+"' AND "+
        "HOSPITAL_CODE LIKE '"+hp+"' AND RECEIVING_BANK_CODE LIKE '"+qy+"'";

        String dat_sum = "SELECT sum(CONVERT(INT,BANK_TMB_MEDIA_CLEARING.TRANSFER_AMOUNT) / 100.00) AS AMOUNT " + //0
        "FROM BANK_TMB_MEDIA_CLEARING WHERE (BATCH_NO IS NULL OR BATCH_NO = '') AND "+
        "YYYY LIKE '"+year+"' AND MM LIKE '"+month+"' AND SERVICE_TYPE LIKE '"+type+"' AND "+
        "HOSPITAL_CODE LIKE '"+hp+"' AND RECEIVING_BANK_CODE like '"+qy+"'";
        boolean status = true;
        //System.out.println(dat);
        String[][] temp_data = null;
        String[][] temp_sum_amount = null ;
        try {
            setFileName(path);//set filename read
            temp_data = d.query(dat);
            temp_sum_amount = d.query(dat_sum);
            if(temp_sum_amount.length>0){
            	sum_dr_amount = temp_sum_amount[0][0];
            	System.out.println(temp_sum_amount[0][0]+"  : "+ sum_dr_amount);
            	
            }
        }catch(Exception e){
            System.out.println(e);
        }

        if(this.getOwnerBank().equals("014")){
        	writeFileNew(generateFileScbBank(temp_data));
        }else if (this.getOwnerBank().equals("011")){
            writeFileNew(bankTMB(temp_data));
        }else if (this.getOwnerBank().equals("025")){
        	writeFileNew(bankBAY(temp_data));
        }else{//UNKNOW UOB BANK CODE
        	writeFileNew(bankUOB(temp_data));
        }
        return status;
    }
    
    private String[] bankBAY(String[][] t){
    	String[] dt = new String[t.length + 1];
    	String dtBankApprove = "";//this.getDate().replaceAll("/", "");
    	long sum = 0;
    	String item_no = "";
    	for(int i = 1; i<=(t.length); i++){
    		sum = sum + Integer.parseInt(t[i-1][12]);
    		//System.out.println("ยอดเงินแต่ละคน = " + Integer.parseInt(t[i-1][12]));
    		//System.out.println("ยอดรวม = " + sum);
    		if(i==1){
    			//item_no = String.valueOf(i) + this.getMm() + this.getYyyy().substring(2, 4);
    			item_no = String.valueOf(i) + "" + "".substring(2, 4);
    		}
        	// DETAIL RECORD********************************************************
    		dt[i] = checkString( "000" ,3,"","b");//3 BRANCH CODE
    		dt[i] = dt[i] + checkString( "001" ,3,"","b");//3 COMPANY ID.
    		dt[i] = dt[i] + checkString( t[i-1][5].substring(1, t[i-1][5].length()) ,10,"","b");//10 ACCOUNT NO.
    		dt[i] = dt[i] + checkString( t[i-1][13].substring(0, ( (t[i-1][13].length()) > 20 ? 20 : t[i-1][13].length() )) ,20," ","b");//20 ACCOUNT NAME
    		dt[i] = dt[i] + checkString( t[i-1][12].substring(1, t[i-1][12].length()) ,11," ","f");//11 AMOUNT
    		dt[i] = dt[i] + checkString( t[i-1][19] ,5," ","b");//5 SEQUENCE NO.
    		dt[i] = dt[i] + checkString( "" ,21," ","b");//21 FILLER
    		//dt[i] = dt[i] + checkString( String.valueOf(i) + this.getMm() + this.getYyyy().substring(2, 4) ,7,"0","f");//7 BATCH NO.
    		dt[i] = dt[i] + checkString( String.valueOf(i) + "" + "".substring(2, 4) ,7,"0","f");//7 BATCH NO.
    		dt[i] = dt[i] + checkString( "" ,48," ","b");//48 FILLER       		
    	}
    	//dt[t.length+1] = "";
    	
    	// CONTROL RECORD********************************************************
    	dt[0] = checkString( "000" ,3,"","b");//3	BRANCH CODE
    	dt[0] = dt[0] + checkString( "001" ,3,"","b");//3	COMPANY ID.
    	dt[0] = dt[0] + checkString( dtBankApprove.substring(0, 4) + dtBankApprove.substring(6, 8) ,6,"","b");//6	POST DATE , t[1][9]
    	dt[0] = dt[0] + checkString( t[1][8].substring(1, t[1][8].length()) ,30," ","b");//30 COMPANY NAME / DEBIT ACCOUNT
    	dt[0] = dt[0] + checkString( "712" ,3,"","b");//3	TRANSACTION CODE
    	dt[0] = dt[0] + checkString( "" ,2," ","b");//2	FILLER
    	dt[0] = dt[0] + checkString( "" ,5," ","b");//5	SEQUENCE NO.
    	dt[0] = dt[0] + checkString( "" ,20," ","b");//20 FILLER
    	dt[0] = dt[0] + checkString( "A" ,1,"","b");//1 BATCH TYPE
    	dt[0] = dt[0] + checkString( item_no ,7,"0","f");//7 BATCH NO.
    	dt[0] = dt[0] + checkString( String.valueOf(t.length) ,7,"0","f");//7 ITEM NO.
    	dt[0] = dt[0] + checkString( String.valueOf(sum) ,15,"0","f");//15 TOTAL AMOUNT
    	dt[0] = dt[0] + checkString( "" ,26," ","b");//26 FILLER 	
    	return dt;
    }
    private String[] bankTMB(String[][] t){
    	String[] dt = new String[t.length];
    	for(int i = 0; i<t.length; i++){
            dt[i] = "TXN";  //Write Default Value of "File Type"
            dt[i] = dt[i]+checkString(t[i][14],120," ","b"); //---------------(02)Payor Name
            dt[i] = dt[i]+checkString(t[i][13],130," ","b"); //---------------(03)Receive Name
            dt[i] = dt[i]+checkString("",40," ","b");        //---------------(04)Mail to Name
            dt[i] = dt[i]+checkString("",40," ","b");        //---------------(05)Address 1
            dt[i] = dt[i]+checkString("",40," ","b");        //---------------(06)Address 2
            dt[i] = dt[i]+checkString("",40," ","b");        //---------------(07)Address 3
            dt[i] = dt[i]+checkString("",40," ","b");        //---------------(08)Address 4
            dt[i] = dt[i]+checkString("",10," ","b");        //---------------(09)Zip Code
            dt[i] = dt[i]+checkString("",16," ","b");        //---------------(10)Cust. Reference
            dt[i] = dt[i]+checkString(t[i][9],8," ","b");    //---------------(11)Effective Date
            dt[i] = dt[i]+checkString("",8," ","b");         //---------------(12)Pickup Date
            dt[i] = dt[i]+checkString("THB",3," ","b");      //---------------(13)Payment Currency
            dt[i] = dt[i]+checkString("",15," ","b");        //---------------(14)Comcode
            dt[i] = dt[i]+checkString("",15," ","b");        //---------------(15)Run Date
            dt[i] = dt[i]+checkString("",20," ","b");        //---------------(16)Vendor Code
            dt[i] = dt[i]+checkString(t[i][8],20," ","b");   //---------------(17)Debit Amount Number
            
            //String amount = ""+JNumber.setFormat(Double.parseDouble(t[i][12]) /100.00,"0.00");
            //dt[i] = dt[i]+checkString(amount,15," ","f");    //---------------(18)Payment Amount
            dt[i] = dt[i]+checkString(JNumber.getMoneyFormat(t[i][12]),15," ","f");//(18)Payment Amount
            dt[i] = dt[i]+checkString(t[i][3]+t[i][4],16," ","b");//----------(19)Bank+Branch Code
            dt[i] = dt[i]+checkString(t[i][5],20," ","b");   //---------------(20)Bank Account
            dt[i] = dt[i]+checkString(t[i][10],2," ","b");   //---------------(21)Pay type "Smart" 04
            dt[i] = dt[i]+checkString("00",2," ","b");       //---------------(22)Baht net "Default 00"
            dt[i] = dt[i]+checkString("",2," ","b");         //---------------(23)Delivery Method
            dt[i] = dt[i]+checkString("",20," ","b");        //---------------(24)Pay Check Location
            dt[i] = dt[i]+checkString("EMAIL",5," ","b");    //---------------(25)Advise Mode
            dt[i] = dt[i]+checkString("",50," ","b");        //---------------(26)Fax No.
            dt[i] = dt[i]+checkString("",50," ","b");        //---------------(27)Email Address
            dt[i] = dt[i]+checkString("",50," ","b");        //---------------(28)Mobile
            dt[i] = dt[i]+checkString("BEN",13," ","b");     //---------------(29)Fee Charge
            if(t[i][3].equals("011")){
            	dt[i] = dt[i]+checkString("DCB",3," ","b");  //---------------(30)Transaction Type
            }else{
            	dt[i] = dt[i]+checkString("MCL",3," ","b");  //---------------(30)Transaction Type
            }
            dt[i] = dt[i]+checkString("",5," ","b");         //---------------(31)Blank
            dt[i] = dt[i]+checkString("",4," ","b");         //---------------(32)Blank
            dt[i] = dt[i]+checkString("",30," ","b");        //---------------(33)Blank
            dt[i] = dt[i]+checkString("",105," ","b");       //---------------(34)Blank
            dt[i] = dt[i]+checkString("",70," ","b");        //---------------(35)Blank
            dt[i] = dt[i]+checkString("",40," ","b");        //---------------(36)Blank
            dt[i] = dt[i]+checkString("",40," ","b");        //---------------(37)Blank
            dt[i] = dt[i]+checkString("",40," ","b");        //---------------(38)Blank
            dt[i] = dt[i]+checkString("",40," ","b");        //---------------(39)Blank
            dt[i] = dt[i]+checkString("",16," ","b");        //---------------(40)Blank
            dt[i] = dt[i]+checkString("",7," ","b");         //---------------(41)Blank
            dt[i] = dt[i]+checkString("",2," ","b");         //---------------(42)Blank
            dt[i] = dt[i]+checkString("",40," ","b");        //---------------(43)Blank
            dt[i] = dt[i]+checkString("END",3," ","b");      //---------------(44)Blank
        }
    	return dt;
    }
    private String[] bankUOB(String[][] temp_data){
    	String[] sub_data = new String[temp_data.length]; 
        for(int i = 0; i<temp_data.length; i++){
            sub_data[i] = "102000001";  //Write Default Value of "File Type"
            
            for(int x = 0; x<temp_data[i].length; x++){
                //System.out.println(temp_data[i].length+" in column : "+x);
                if(x == 3){        //---------------------------------------Receive Bank Code
                    sub_data[i] = sub_data[i] + checkString(temp_data[i][x],3," ","b");
                }else if(x == 4){  //---------------------------------------Receive Bank Branch
                    sub_data[i] = sub_data[i] + checkString(temp_data[i][x],4,"0","f");
                }else if(x == 5){  //---------------------------------------Receive Account No.
                    sub_data[i] = sub_data[i] + checkString(temp_data[i][x],11,"0","f");
                }else if(x == 6){  //---------------------------------------Send Bank Code
                    sub_data[i] = sub_data[i] + checkString(temp_data[i][x],3," ","b");
                }else if(x == 7){  //---------------------------------------Send Bank Branch
                    sub_data[i] = sub_data[i] + checkString(temp_data[i][x],4,"0","f");
                }else if(x == 8){  //---------------------------------------Send Account No.
                    sub_data[i] = sub_data[i] + checkString(temp_data[i][x],11,"0","f");
                }else if(x == 9){  //---------------------------------------Effective Date
                    sub_data[i] = sub_data[i] + checkString(temp_data[i][x],8," ","b");
                }else if(x == 10){  //--------------------------------------Service Type
                    sub_data[i] = sub_data[i] + checkString(temp_data[i][x],2," ","b");
                }else if(x == 11){  //--------------------------------------Clearing House Code
                    sub_data[i] = sub_data[i] + "00";
                }else if(x == 12){  //--------------------------------------Transfer Amount
                    sub_data[i] = sub_data[i] + checkString(temp_data[i][x],12,"0","f");
                }else if(x == 13){  //--------------------------------------Receive Information
                    //new String(sub_data[11].trim().getBytes(),"TIS-620")
                    sub_data[i] = sub_data[i] + checkString(temp_data[i][x],60," ","b");
                }else if(x == 14){  //--------------------------------------Sender Information
                    sub_data[i] = sub_data[i] + checkString(temp_data[i][x],60," ","b");
                }else if(x == 15){  //--------------------------------------Other Information
                    sub_data[i] = sub_data[i] + checkString(temp_data[i][x],100," ","b");
                }else if(x == 16){  //--------------------------------------Running No.
                    sub_data[i] = sub_data[i] + checkString(temp_data[i][x],6,"0","f");
                    //sub_data[i] = sub_data[i] + checkString(""+i,6,"0","f"); //Running from this method
                }else if(x == 17){  //--------------------------------------Space
                    sub_data[i] = sub_data[i] + checkString("",16," ","f");
                }else if(x == 18){  //--------------------------------------Company Code
                    sub_data[i] = sub_data[i] + checkString(temp_data[i][x],4," ","b");
                }else if(x == 19){  //--------------------------------------Filler
                    sub_data[i] = sub_data[i] + checkString(temp_data[i][x],5,"0","f");
                }else{}
            }
        }
        return sub_data;
    }
    private String[] generateFileScbBank(String[][] temp_data){
    	String[] sub_data = new String[temp_data.length+1]; 
        for(int i = 1; i<=(temp_data.length); i++){
        	sub_data[i] = "";  //Write Default Value of "File Type"
        	  String[] temps = null;
              String keep_doubles = "";
              keep_doubles = ""+ temp_data[i-1][12];
          	temps = keep_doubles.split("[.]");
          	if(temps.length>1){
          		//System.out.println(keep_double);
              	keep_doubles = temps[1];
              	if(keep_doubles.length()<2){
              		temps[1] = temps[1]+"0";
              	}else if(keep_doubles.length()>2){
              		temps[1] = keep_doubles.substring(0,2);
              	}else{}
              	keep_doubles = temps[0]+temps[1];
              	//sum1 = Double.parseDouble(keep_double);
          	}else{
          	}
            for(int x = 0; x<temp_data[i-1].length; x++){
            	if(x == 5){  //---------------------------------------Account No.
            		if(temp_data[i-1][x].length()>10){
                    sub_data[i] = sub_data[i] + checkString(temp_data[i-1][x].substring(1, 11),10,"","b");
            		}
            		else{
            			sub_data[i] = sub_data[i] + checkString(temp_data[i-1][x],10,"","b");
            		}		
                }else if(x == 9){  //---------------------------------------Effective Date
                    sub_data[i] = sub_data[i]+ checkString(temp_data[i-1][9].substring(0,4)+temp_data[i-1][9].substring(6,8),6,"0","f");
                }else if(x == 11){  //--------------------------------------SIGN
                    sub_data[i] = sub_data[i] + checkString("",1,"0","f");
                }else if(x == 12){  //--------------------------------------Amount
                	sub_data[i] = sub_data[i] + checkString(keep_doubles,9,"0","f");
                	//System.out.println(keep_doubles);
                }else if(x == 13){  //--------------------------------------SPACE
                    sub_data[i] = sub_data[i] + checkString("",52," ","f");
                }
                else{}
            }
        }
        String[] temp = null;
        String keep_double = "";
        keep_double = ""+ sum_dr_amount;
    	temp = keep_double.split("[.]");
    	if(temp.length>1){
    		//System.out.println(keep_double);
        	keep_double = temp[1];
        	if(keep_double.length()<2){
        		temp[1] = temp[1]+"0";
        	}else if(keep_double.length()>2){
        		temp[1] = keep_double.substring(0,2);
        	}else{}
        	keep_double = temp[0]+temp[1];
        	//sum1 = Double.parseDouble(keep_double);
    	}else{
    	}
    	
        sub_data[0] = checkString("",9," ","f");//------------------------------------SPACE
        sub_data[0]	= sub_data[0]+ checkString("",1,"M","b");//-----------------------TERM-PAYMENT
        sub_data[0] = sub_data[0]+ checkString("",1,"052028","f"); //--------COMPANY-CODE
        sub_data[0] = sub_data[0]+ checkString(temp_data[0][14],30," ","b");//-------COMPANY-NAME
        sub_data[0] = sub_data[0]+ checkString( "",1,"D","b");//----------------------APPLY-CODE
        sub_data[0] = sub_data[0]+ checkString( "",1,"A","b");//----------------------STATUS
        sub_data[0] = sub_data[0]+ checkString(temp_data[0][9].substring(0,4)+temp_data[0][9].substring(6,8),6,"0","f");//----------Effective Date
        sub_data[0] = sub_data[0]+ checkString(String.valueOf(temp_data.length),5,"0","f");//-- TOTAL-RECORD
        sub_data[0] = sub_data[0]+ checkString( String.valueOf(keep_double),10,"0","f");//--TOTAL-AMOUNT
        sub_data[0]	= sub_data[0]+ checkString("",1,"I","b");//-----------------------MEDIA-TYPE
        sub_data[0] = sub_data[0]+ checkString("",8," ","f");//----------------------- SPACE
        return sub_data;
    }   
}
